package cn.whit.blog.dao.impl;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.whit.blog.dao.TblogDao;
import cn.whit.blog.domain.StatisticalType;
import cn.whit.blog.domain.Tblog;
import cn.whit.blog.util.DynamicQuery;
import cn.whit.blog.util.JDBCUtils;
import cn.whit.blog.util.Parameter;

public class TblogDaoImpl implements TblogDao{

	@Override
	public Tblog findById(String id) throws SQLException {
		String sql="select * from tblog where id=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Tblog>(Tblog.class),id);
	}

	@Override
	public List<Tblog> findAll() throws SQLException {
		String sql="select * from tblog";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Tblog>(Tblog.class));
	}
	
	@Override
	public int findTotalRecords() throws Exception {
		String sql="select count(*) from tblog";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}
	
	@Override
	public List<Tblog> findAllWithPage(int startIndex, int pageSize) throws Exception {
		String sql="select * from tblog order by id desc limit  ? , ?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Tblog>(Tblog.class),startIndex,pageSize);
		
	}
	
	@Override
	public int findTotalRecordsAndQuery(DynamicQuery query) throws Exception {
		String sql="select count(*) from tblog where 1=1 ";
		query.setBaseSql(sql);
		sql = query.generateSql();
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		ArrayList<Parameter> parameters = query.getParameters();
		Object[] objs = new Object[parameters.size()];
		int i = 0;
		for (Parameter p : parameters) {
			objs[i] = p.getValue();
			i++;
		}
		Long num=(Long)qr.query(sql, new ScalarHandler(),objs);
		return num.intValue();
	}
	
	@Override
	public List<Tblog> findAllWithPageAndQuery(int startIndex, int pageSize,DynamicQuery query) throws Exception {
		String sql="select * from tblog where 1=1 ";
		query.setBaseSql(sql);
		sql = query.generateSql();
		sql += " order by id desc limit  ? , ? ";
		
        //如果想排序，自行在sql后添加   
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		
		ArrayList<Parameter> parameters = query.getParameters();
		Object[] objs = new Object[parameters.size() + 2];
		int i = 0;
		for (Parameter p : parameters) {
			objs[i] = p.getValue();
			i++;
		}
		objs[i] = startIndex;
		objs[i+1] = pageSize;
		
		return qr.query(sql, new BeanListHandler<Tblog>(Tblog.class),objs);
	}

	@Override
	public List<Tblog> selectByQuery(DynamicQuery query) {
		// TODO Auto-generated method stub
		return null;
	}

//	@Override
//	public void save(Tblog tblog) throws SQLException {
//		String sql="INSERT INTO tblog(title,content,publishTime,updateTime,userid,status,picurls,username) VALUES(?,?,?,?,?,?,?,?)";
//		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
//		Object[] params={tblog.getTitle(),tblog.getContent(),tblog.getPublishTime(),
//				tblog.getUpdateTime(),tblog.getUserid(),tblog.getStatus(),tblog.getPicurls(),tblog.getUsername()};
//		qr.update(sql,params);
//	}
	
	
	@Override
	public long save(Tblog tblog) throws SQLException {
		//改写了上面的save方法，返回新插入的blog的id值（数据库自增长的）
		Connection conn = JDBCUtils.getConnection();
		PreparedStatement ps = conn.prepareStatement(
	            "INSERT INTO tblog (title,content,publishTime,updateTime,userid,status,picurls,username,tblogtypeid) VALUES (?,?,?,?,?,?,?,?,?)",
	            Statement.RETURN_GENERATED_KEYS);
		ps.setObject(1,tblog.getTitle());  
        ps.setObject(2, tblog.getContent());  
        ps.setObject(3, tblog.getPublishTime()); 
        
        ps.setObject(4, tblog.getUpdateTime());
        ps.setObject(5, tblog.getUserid());
        ps.setObject(6, tblog.getStatus());
        ps.setObject(7, tblog.getPicurls());
        ps.setObject(8, tblog.getUsername());
        ps.setObject(9, tblog.getTblogtypeid());
        
        int n = ps.executeUpdate(); // 1
        
        ResultSet rs = ps.getGeneratedKeys();  //多行多列，去首行首列
        
        if (rs.next()) {
            long id = rs.getLong(1); // 注意：索引从1开始
            System.out.println("新博客数据的id是: " + id);
            return id;
        }
        return 0;
		
	}

	@Override
	public void updateNum(String id,  StatisticalType statisticalType) throws SQLException {
		String sql = "";
		if(statisticalType == StatisticalType.Dianzan) {
			sql = "update tblog set votes = votes + 1  WHERE id=? ";
		}
		Object[] params={id};
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);
	}

	@Override
	public void deleteById(String s) throws SQLException {
		String sql=" delete from tblog where id = ? ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={s};
		qr.update(sql,params);
	}

	@Override
	public List<Tblog> findByUserId(String s) throws SQLException {
		String sql="select * from tblog where userid=? ";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Tblog>(Tblog.class),s);
	}
	
}
